1.1 Protocol Report

1.2 Table 15. Data and summary statistics for annual report

2.1 Figure E1. Map of locations

library(tidyverse)
library(leaflet)
library(mapview)
library(glue)
library(sf)

dir_tbl <- "~/Google Drive/projects/nps-ecoquants/data/CHISLandVegetationMonitoringDatabase/tables"

to_lonlat <- function(x, y, crs_str){
  st_as_sf(data_frame(x=x, y=y), crs=crs_str, coords = c("x", "y")) %>%
    st_transform(crs=4326) %>%
    st_coordinates()
}

locations <- read_csv(file.path(dir_tbl, "tbl_Locations.txt")) %>%
  mutate(
    # fix one location
    Y_Coord   = ifelse(Location_ID == 1502833764, B_Y_Coord, Y_Coord)) %>%
  # TODO: fix other locations
  filter(!is.na(X_Coord), !is.na(Y_Coord), !is.na(UTM_Zone)) %>%
  mutate(
    crs_str = glue("+proj={tolower(Coord_System)} +zone={UTM_Zone} +datum={Datum} +units={c(meters='m')[Coord_Units]}"),
    lon_lat = pmap(list(X_Coord, Y_Coord, crs_str), to_lonlat),
    lon     = map_dbl(lon_lat, ~.[,1]),
    lat     = map_dbl(lon_lat, ~.[,2])) %>%
  st_as_sf(coords = c("lon", "lat"), crs=4326)

mapView(locations)

2.2 Figure E1. Map of locations - clustered

leaflet(locations) %>%
  addProviderTiles(providers$Esri.OceanBasemap) %>%
  addMarkers(
    clusterOptions = markerClusterOptions(),
    popup = ~glue(
      "<b>Location_ID</b>: {Location_ID}<br>
      <b>Vegetation_Community</b>: {Vegetation_Community}<br>
      <b>Loc_Notes</b>: {Loc_Notes}"))

2.3 Figure E2. Absolute foliar cover (%) - Static

library(readxl)
library(here)

dir_qry         <- "~/Google Drive/projects/nps-ecoquants/data/CHISLandVegetationMonitoringDatabase/queries"
fig_codes_csv   <- here("data/figure_codes.csv")
q_xlsx          <- file.path(dir_qry, "qry-2016_fig-E2-absolute-cover.xlsx")
park            <- "CHIS"
year            <- 2016
veg_communities <- c("Coastal Sage Scrub", "Island Chaparral", "Valley/Foothill Grassland")

q <- readxl::read_excel(q_xlsx, na = c("", "NA")) %>%
  arrange(Park, SurveyYear, IslandCode, Vegetation_Community, FxnGroup, Nativity)
# View(q)
# table(q$IslandCode)
# table(q$Vegetation_Community)
# table(q$FxnGroup)

d <- q %>%
  filter(
    !is.na(Vegetation_Community), !is.na(FxnGroup), !is.na(Average), !is.na(Nativity)) %>%
  # NOTE: aggregating across islands
  group_by(SurveyYear, Park, Vegetation_Community, FxnGroup, Nativity) %>%
  summarize(
    pct_avg = mean(Average),
    pct_min = mean(MinRange),
    pct_max = mean(MaxRange)) %>%
  arrange(Vegetation_Community, FxnGroup, Nativity) %>%
  filter(Vegetation_Community %in% veg_communities)
# View(d)

#fill=FxnGroup, group=FxnGroup, color=FxnGroup, 
g <- ggplot(d, aes(x=Vegetation_Community, y=pct_avg, fill=FxnGroup, alpha=Nativity)) + 
  #facet_wrap(~Vegetation_Community) +
  geom_bar(stat="identity", position="dodge") + # , colour="black"
  scale_alpha_manual(values=c(1, 0.3)) +
  labs(x="Vegetation Community", y="Cover (%)") #+
  #coord_flip()

# TODO: FxnGroup = "All Sites"
g

2.4 Figure E2. Absolute foliar cover (%) - Dynamic

library(plotly)

ggplotly(g)

2.5 Table E6. Species by site

library(DT)
library(lubridate)

dir_tbl      <- "~/Google Drive/projects/nps-ecoquants/data/CHISLandVegetationMonitoringDatabase/tables"

events       <- read_csv(file.path(dir_tbl, "tbl_Events.txt"))       # View(events)
event_points <- read_csv(file.path(dir_tbl, "tbl_Event_Point.txt"))  # View(event_points)
species_data <- read_csv(file.path(dir_tbl, "tbl_Species_Data.txt")) # View(species_data)
#project_taxa <- read_csv(file.path(dir_tbl, "tlu_Project_Taxa.txt"))
locations    <- read_csv(file.path(dir_tbl, "tbl_Locations.txt"))    # View(locations)
sites        <- read_csv(file.path(dir_tbl, "tbl_Sites.txt"))        # View(sites)

d <- events %>%
  mutate(
    start_date = as.Date(Start_Date, "%m/%d/%Y %H:%M:%S")) %>%
  left_join(
    event_points, by="Event_ID") %>%
  left_join(
    species_data, by="Event_Point_ID") %>%
  #left_join(
  #  project_taxa, by=c("Species_Code"="Species_code"))
  left_join(
    locations, by="Location_ID") %>%
  left_join(
    sites, by="Site_ID") %>%
  filter(year(start_date) == 2016) %>%
  group_by(Site_Desc, Species_Code) %>%
  summarize(
    n = n())

datatable(d)

2.6 Table E6. Species richness by site - static

d %>%
  group_by(Site_Desc) %>%
  summarize(
    n_species = n()) %>%
  kable(format.args = list(big.mark = ","))

3.1 MS Access DB on Windows

• Fixed VBA errors with 32-bit Declare Function to 64-bit Private Declare PtrSafe Function using MS Access 2013

Windows only connection option:

library(RODBC)

accdb <- "Z:/bbest On My Mac/Google Drive/projects/nps-ecoquants/data/CHISLandVegetationMonitoringDatabase/LandVegetationMonitoring_DATA_be.accdb"

odbcDataSources()
db <- odbcConnectAccess2007(accdb)

sqlTables(db)
sqlQuery("SELECT * FROM tbl_Locations")

3.2 MS Access DB & Postgres

Connect Access Front-End to Postgres Backend:

• Connect Microsoft Access to PostgreSQL - iShare Help - Confluence
• Using MS Access with PostgreSQL - Postgres OnLine Journal

Assistant applications to handle conversion / synchronization:

• Access To PostgreSQL $49
• Access To PostgreSQL: DBConvert $149, DBSync $149

3.3 Create New Front-End App

For example, with Shiny:

• Creating Interactive Web Applications with R & Shiny

Working with databases & Shiny:

• Databases using R
• Shiny - Database basics - dplyr and DBI
• Shiny - Persistent data storage in Shiny apps
• Enterprise-ready dashboards with Shiny and databases · R Views
• Create an R Shiny Database CRUD app

4.1 Rmarkdown formats

From the same Rmarkdown document:

• index.Rmd

You can generate these (and more):

• ioslides_presentation
• html_document
• pdf_document
• word_document